package com.web.new12306.dao.impl;

import java.sql.Timestamp;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.web.new12306.dao.OrderDao;
import com.web.new12306.pojo.Order;
import com.web.new12306.pojo.Page;
import com.web.new12306.pojo.Page1;
import com.web.new12306.pojo.Ticket;

public class OrderDaoImpl implements OrderDao {
	/**
	 * @author 赵靖懿
	 */
	
	Connection conn = null;
	PreparedStatement pstmt = null;
	ResultSet rs = null;

	@Override
	public int number(Connection conn) {
		// TODO Auto-generated method stub
		String sql="select count(*) from orders";
		int n=0;
		try {
			this.conn=conn;
			pstmt=conn.prepareStatement(sql);
			rs=pstmt.executeQuery();
			while(rs.next())
			{
				n=rs.getInt(1);
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return n;
	}

	@Override
	public List<Order> select(Connection conn, Integer page, Integer rows) {
		// TODO Auto-generated method stub
		String sql = "select * from orders limit ?,?";
		List<Order> list = new ArrayList<Order>();
		try {
			this.conn=conn;
			pstmt = conn.prepareStatement(sql);
			pstmt.setInt(1,(page-1)*rows);
			pstmt.setInt(2, page*rows);
			ResultSet rs = pstmt.executeQuery();
			while (rs.next()) {
				Order order=new Order();
				order.setOrderId(rs.getInt("orderId"));
				order.setUsername(rs.getString("username"));
				order.setTrainNumber(rs.getString("trainNumber"));
				order.setStartStation(rs.getString("startStation"));
				order.setTerminalStation(rs.getString("terminalStation"));
				order.setTime(rs.getTimestamp("time"));
				order.setPrice(rs.getInt("price"));
				order.setDealTime(rs.getTimestamp("dealTime"));
				order.setStatus(rs.getString("status"));
				list.add(order);

			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}

		return list;
	}

	/**
	 * @author 黄灵莉
	 */
	public Page1 showTickets(Connection conn, Ticket ticket, int page, int rows) {
		// TODO Auto-generated method stub
		this.conn=conn;
		Page1 page1=null;
		Ticket ticket2=null;
		List<Ticket> list=new ArrayList<Ticket>();
		StringBuffer sbsql=new StringBuffer();
		sbsql.append(" select * from ticket ");
		sbsql.append(" where remainingTickets >0 ");
		//起点站
		String startStation=ticket.getStartStation();
		System.out.println(startStation);
		if(startStation!=null&&!startStation.equals("")) {
			sbsql.append(" and startStation='"+startStation+" '");
		}
		//终点站
		String terminalStation=ticket.getTerminalStation();
		if(terminalStation!=null&&!terminalStation.equals("")) {
			sbsql.append(" and terminalStation='"+terminalStation+"'");
			
		}
		//发车时间
		Timestamp time=ticket.getTime();
		if(time!=null&&!time.equals("")) {
			String time1=time.toString();
			String st=time1.substring(0, 10);
		System.out.println(st);
			sbsql.append(" and to_char(time,'yyyy-MM-dd')='"+st+"'");
		}
		String st=sbsql.toString();
		System.out.println(st);
		//String sql11="select * from (select @rownum:=@rownum+1 as rownum,ticket.* from ("+st+" ),(select @rownum:=0) order by ticketId asc)a limit ?,?";
		String sql1="select @rownum:=@rownum+1 as rownum,t.* from ("+st+") t,(select @rownum:=0) tt order by ticketId asc limit ?,?";
		System.out.println(sql1);
		try {
			pstmt=conn.prepareStatement(sql1);
			pstmt.setInt(1, (page-1)*rows);
			pstmt.setInt(2, page*rows);
			rs=pstmt.executeQuery();
			while(rs.next()) {
				ticket2=new Ticket();
				ticket2.setTicketId(rs.getInt("ticketId"));
				ticket2.setStartStation(rs.getString("startStation"));
				ticket2.setTerminalStation(rs.getString("terminalStation"));
				ticket2.setPrice(rs.getInt("price"));
				ticket2.setTime(rs.getTimestamp("time"));
				ticket2.setRemainingTickets(rs.getInt("remainingTickets"));
				ticket2.setTrainNumber(rs.getString("trainNumber"));
				//ticket2.setArrivalTime(rs.getTimestamp("arrivalTime"));
				list.add(ticket2);

			}
			int totalRowcount=getTicketRowCount(conn, st);
			 page1=new Page1(list,totalRowcount);
			System.out.println(totalRowcount );
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return page1;
	}

	/**
	 * @author 黄灵莉
	 */
	public int getTicketRowCount(Connection conn, String sql) {
		// TODO Auto-generated method stub
		this.conn=conn;
		int rowcount=0;
		try {
			String sql1="select count(*) from ("+sql+") a";

			pstmt=conn.prepareStatement(sql1);
			rs=pstmt.executeQuery();
			if (rs.next()) {
				//增加计数
				rowcount = rs.getInt(1);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return rowcount;
	}

	/**
	 * @author 黄灵莉
	 */
	public Ticket getTicketByTicketId(Connection conn, int ticketId) {
		// TODO Auto-generated method stub
		Ticket ticket2=null;
		this.conn=conn;
		String sql="select * from ticket where ticketId=?";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1,ticketId);
			rs=pstmt.executeQuery();
			while(rs.next()) {
				ticket2=new Ticket();
				ticket2.setTicketId(rs.getInt("ticketId"));
				ticket2.setStartStation(rs.getString("startStation"));
				ticket2.setTerminalStation(rs.getString("terminalStation"));
				ticket2.setPrice(rs.getInt("price"));
				ticket2.setTime(rs.getTimestamp("time"));
				ticket2.setRemainingTickets(rs.getInt("remainingTickets"));
				ticket2.setTrainNumber(rs.getString("trainNumber"));
				//ticket2.setArrivalTime(rs.getTimestamp("arrivalTime"));
			}
			
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return ticket2;
	}

	/**
	 * @author 黄灵莉
	 */
	public int addOrder(Connection conn, Order order) {
		// TODO Auto-generated method stub
		this.conn=conn;
		int r=0;
		String sql="insert into orders (username,trainNumber,startStation,terminalStation,time,price,dealTime,status) values(?,?,?,?,?,?,now(),'已预订 ')";
		try {
			pstmt=conn.prepareStatement(sql);
			pstmt.setString(1, order.getUsername());
			pstmt.setString(2, order.getTrainNumber());
			pstmt.setString(3, order.getStartStation());
			pstmt.setString(4, order.getTerminalStation());
			pstmt.setTimestamp(5, order.getTime());
			pstmt.setInt(6, order.getPrice());
			r=pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		
		return r;
	}

}
